package com.bookm.service;

import com.alibaba.fastjson.JSONObject;
import com.bookm.bean.ImportBean;
import com.bookm.bean.ImportColumnInfo;
import com.bookm.bean.ImportStatusDTO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@Component
public abstract class BaseImportExeclServiceAbsImpl implements Runnable, ImportExeclService {
    public static void main(String[] args) {
        ImportBean importbean = new ImportBean();
        importbean.funid = 1;
        importbean.userid = String.valueOf(1);
        importbean.compid = 1;
        importbean.impType = 1;
//        importbean.importfile = importfile;
        importbean.importParams = "type,name";
        importbean.language = "CN";
        if (!StringUtils.isEmpty(importbean.importParams)) {
            importbean.importParams_list = importbean.importParams.split(";");
        }

        BookImportXlsService empRptRosterHisImportXlsService = new BookImportXlsService();
        empRptRosterHisImportXlsService.setImp_bean(importbean);
        empRptRosterHisImportXlsService.run();

    }

    private Logger logger = LoggerFactory.getLogger(BaseImportExeclServiceAbsImpl.class);
    protected ImportBean imp_bean;
    protected ImportStatusDTO imp_status;
    protected int nullDate;
    // 记录导入行错误信息
    protected ArrayList<String[]> imp_rows_error;
    ;
    //tgtemp路径
    protected String tgTempPath = System.getProperty("user.dir") + File.separator + "tstemp" + File.separator;
    protected String impfileAllName = String.format("%simport_%s.xlsx", tgTempPath, System.currentTimeMillis());


    public ImportBean getImp_bean() {
        return imp_bean;
    }

    public void setImp_bean(ImportBean imp_bean) {
        this.imp_bean = imp_bean;
    }

    public void run() {
        long t1_1 = System.currentTimeMillis();
        imp_rows_error = new ArrayList<String[]>();
        imp_status = new ImportStatusDTO();
        File file = new File(tgTempPath);
        if (!file.exists()) {
            file.mkdir();
        }
        try {
            dealImport("data.xlsx");
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            // 更新导入标记
            setImpflag();
        }
        long t1_2 = System.currentTimeMillis();
        logger.info("导入总用时" + (t1_2 - t1_1));
    }

    protected void dealImport(String filefname) throws Exception {
        File file = null;
        InputStream is = null;
        boolean isExcel2003 = false;
        try {
            setImportStatus(0, "");
            // 保存文件
            setImportStatus(5, "");

            // 前置处理
            importBefore();
            setImportStatus(5, "");

            HashMap<String, ImportColumnInfo> icHash = new HashMap<String, ImportColumnInfo>();

            // 设置导入字段
            long t4_1 = System.currentTimeMillis();
            // todo 设置需要导入的字段
            setImportColumn(icHash);
            long t4_2 = System.currentTimeMillis();
            logger.info("设置导入字段用时" + (t4_2 - t4_1));
            long t8_1 = System.currentTimeMillis();
            org.apache.poi.ss.usermodel.Workbook wb = null;
            Sheet sheet = null;
            Row rows = null;
            Cell cell = null;

            file = new File(filefname);
            is = new FileInputStream(file);

            // todo  读取excel信息
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
                sheet = (HSSFSheet) wb.getSheetAt(0);
            } else {
                wb = new XSSFWorkbook(is);
                sheet = (XSSFSheet) wb.getSheetAt(0);
            }
            int startline = 1;
            if (isExcel2003)
                rows = (HSSFRow) sheet.getRow(startline - 1);
            else
                rows = (XSSFRow) sheet.getRow(startline - 1);
            int cs = rows.getFirstCellNum();
            int ce = rows.getLastCellNum();
            long t8_2 = System.currentTimeMillis();
            logger.info("创建wb用时" + (t8_2 - t8_1));
            // todo 获得首行信息 excel列
            long t5_1 = System.currentTimeMillis();
            for (int s1 = cs; s1 < ce; s1++) {
                if (isExcel2003)
                    cell = (HSSFCell) rows.getCell(s1);
                else
                    cell = (XSSFCell) rows.getCell(s1);
                if (cell != null) {
                    String column = cell.getStringCellValue();
                    column = (column == null) ? "" : column.trim();
                    if (!sheet.isColumnHidden(s1) && column.length() > 0) {

                        // todo 控制在配置的导入字段中， 获取当前列名去匹配， 并且设置icHash当前的列名对应的列
                        if (!isExist(column, icHash))
                            imp_status.imp_rows_nomatch += column + ",";
                        for (String key : icHash.keySet()) {
                            ImportColumnInfo ic = icHash.get(key);
                            if ((ic.ici_xls_title.equalsIgnoreCase(column))) {
                                ic.ici_xls_cols = s1;
                                break;
                            }
                        }
                    }
                }
            }
            String string = icHash.toString();

            long t5_2 = System.currentTimeMillis();
            logger.info("获得excel列用时" + (t5_2 - t5_1));
            long t6_1 = System.currentTimeMillis();
            // 写入数据
            SimpleDateFormat df;
            NumberFormat nf = new DecimalFormat("#.####");
            int ttcount = sheet.getLastRowNum();
            JSONObject jsonObject;
            int tmp_impps = 0;
            if (ttcount > 0) {

                // todo 从第二行开始遍历
                for (int j = startline; j <= sheet.getLastRowNum(); j++) {
                    //long t10_1 = System.currentTimeMillis();
                    if (isExcel2003)
                        rows = (HSSFRow) sheet.getRow(j);
                    else
                        rows = (XSSFRow) sheet.getRow(j);
                    if (rows != null) {
                        imp_status.imp_totals += 1;
                        boolean isBank = true;
                        jsonObject = new JSONObject();
                        String v;
                        try {
                            // todo 在我的key匹配列表里面，并且有列信息
                            //  将数据存储到jsonObject中
                            for (String key : icHash.keySet()) {
                                ImportColumnInfo ic = icHash.get(key);
                                if (ic.ici_xls_cols == -1 || ic.ici_xls_title.length() == 0)
                                    continue;
                                v = "";
                                if (isExcel2003)
                                    cell = (HSSFCell) rows.getCell(ic.ici_xls_cols);
                                else
                                    cell = (XSSFCell) rows.getCell(ic.ici_xls_cols);
                                if (cell != null) {

                                    // todo 获取其中的数值
                                    try {
//										if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // 数值
//											if (HSSFDateUtil.isCellDateFormatted(cell)) {
//												if (ic.ici_xls_type.equalsIgnoreCase("Date")) {
//													df = new SimpleDateFormat("yyyy-MM-dd");
//													v = df.format(cell.getDateCellValue());
//												} else if (ic.ici_xls_type.equalsIgnoreCase("Datetime")) {
//													df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//													v = df.format(cell.getDateCellValue());
//												} else {
//													v = cell.getStringCellValue();
//												}
//											} else
//												v = nf.format(cell.getNumericCellValue());
//										} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { // 公式
//											try {
//												v = nf.format(cell.getNumericCellValue());
//											} catch (Exception e) {
//												v = cell.getStringCellValue();
//											}
//										} else
                                        v = cell.getStringCellValue();
                                    } catch (Exception e) {
                                        throw new Exception("列(" + ic.ici_xls_title + ") Error: " + e.getMessage());
                                    }
                                    if (v != null) {
                                        v = v.trim();
                                    }
                                    if (ic.ici_xls_type.equalsIgnoreCase("Boolean") && v.length() > 1)
                                        v = v.substring(0, 1);
                                }

                                if (ic.ici_xls_type.equalsIgnoreCase("Number") && v.length() == 0)
                                    v = "0";
                                // todo 将数据存储到jsonObject中
                                jsonObject.put(ic.ici_column, v);
                                if (!StringUtils.isBlank(v))
                                    isBank = false;
                            }
                            if (!isBank) {
                                // 导入数据格式化为对象，并检查主键及非空项
                                Object object = formatObject(jsonObject);
                                // 导入特殊检查，返回特殊异常，如果被捕获，直接退出导入
                                import_check(object);

                                // 导入数据处理
                                importData(object);
                            } else {
                                nullDate += 1;
                            }
                            //TODO
//						} catch (BusinessException e) {
//							if (imp_bean.impType == 1) {
//								throw new Exception("记录已存在，不能新增！");
//							} else if (imp_bean.impType == 2) {
//								throw new Exception("记录不存在，不能修改！");
//							}
                        } catch (Exception e) {
                            imp_status.imp_fails += 1;
                            String[] errrow = new String[2];
                            errrow[0] = String.valueOf(j);
                            errrow[1] = e.getMessage();
                            imp_rows_error.add(errrow);
                            // 如果import_check返回特殊异常，直接退出
                            if (errrow[1] != null && errrow[1].equalsIgnoreCase("pyperiod-exception")) {
                                throw new Exception("导入的年度与选择的年度不一致，请检查导入数据！");
                            }
                        }
                    }

                    int newimps = (int) Math.floor((float) imp_status.imp_totals / (float) ttcount * 75.0);
                    setImportStatus(newimps - tmp_impps, "");
                    tmp_impps = newimps;
                    //long t10_2 = System.currentTimeMillis();
                    //logger.info("每条业务逻辑用时" + (t10_2 - t10_1));
                }
                imp_status.imp_totals -= nullDate;
                // 导入后置处理
                importAfter();  // todo 坑爹的地方。即使import的部分数据失败了，其余的部分仍然尝试importAfter
                setImportStatus(5, "");
            } else { // 空数据
                setImportStatus(80, "");
            }

            long t6_2 = System.currentTimeMillis();
            logger.info("业务逻辑用时" + (t6_2 - t6_1));
            // 设置导入返回
            long t7_1 = System.currentTimeMillis();
            setImportReturn(icHash, startline, isExcel2003);
            long t7_2 = System.currentTimeMillis();
            logger.info("设置导入返回用时" + (t7_2 - t7_1));
            setImportStatus(10, "");
        } catch (Exception e) {
            setImportStatus(-1, e.getMessage());
            throw e;
        } finally {
            if (is != null)
                is.close();
            if (file != null)
                file.delete();
        }
    }

    // 设置导入状态
    protected void setImportStatus(int add_progress, String errifo) {
        // 判断，如果已经处理为失败，则不在处理 2023-3-24 darren update
        // 2023-3-24 darren update

        if (StringUtils.isNotBlank(imp_status.imp_errfile)) {
            imp_status.imp_status = 100;
            imp_status.imp_fails = imp_status.imp_totals - imp_status.imp_success;
        } else {
            if (add_progress < 0)
                imp_status.imp_status = add_progress;
            else
                imp_status.imp_status += add_progress;
        }
        boolean recordsJobQueueStatus = true;
        if (imp_status.imp_status == 10) {
            recordsJobQueueStatus = false;
        }
        if (imp_status.imp_status > 10 && imp_status.imp_status < 85) {
            if (imp_status.imp_status % 5 == 0)
                recordsJobQueueStatus = true;
            else
                recordsJobQueueStatus = false;
        }
        switch (imp_status.imp_status) {
            case -1:
                if (!StringUtils.isBlank(errifo) && errifo.length() < 500) {
                    imp_status.imp_status_desc = "导入失败，错误信息：" + errifo;
                } else {
                    imp_status.imp_status_desc = "导入失败";
                }
                break;
            case 0:
                imp_status.imp_status_desc = "数据文件上传中...";
                break;
            case 100:
                imp_status.imp_status_desc = "导入已完成！";
                if (StringUtils.isNotBlank(imp_status.imp_errfile)) {
                    imp_status.imp_status_desc = "导入完成，存在错误，请查阅失败文件！";
                }
                break;
            default:
                imp_status.imp_status_desc = "文件已上传，数据导入中，已完成" + imp_status.imp_status + "%。";
                break;
        }

        logger.info("导入状态: {}", imp_status.imp_status_desc);

        Map<String, String> imp_redis_map = new HashMap<String, String>();
        imp_redis_map.put("imp_totals", String.valueOf(imp_status.imp_totals));
        imp_redis_map.put("imp_success", String.valueOf(imp_status.imp_success));
        imp_redis_map.put("imp_fails", String.valueOf(imp_status.imp_fails));
        imp_redis_map.put("imp_rows_nomatch", imp_status.imp_rows_nomatch);
        imp_redis_map.put("imp_errfile", imp_status.imp_errfile);
        imp_redis_map.put("imp_status", String.valueOf(imp_status.imp_status));
        imp_redis_map.put("imp_status_desc", imp_status.imp_status_desc);

        if (recordsJobQueueStatus) {
        }
    }

    // 保存导入文件
//    protected String saveImportFile() throws Exception {
//        File file = null;
//        FileOutputStream os = null;
//        try {
//            byte[] b = fileHandleService.download(true, imp_bean.importfile);
//            if (b != null) {
//                file = new File(impfileAllName);
//                if (!file.exists())
//                    file.createNewFile();
//                os = new FileOutputStream(file);
//                os.write(b);
//            } else
//                throw new Exception("file not exists!");
//        } catch (Exception e) {
//            throw e;
//        } finally {
//            if (os != null)
//                os.close();
//        }
//        return impfileAllName;
//    }

    // 设置导入返回
    protected void setImportReturn(HashMap<String, ImportColumnInfo> icHash, int startline, boolean isExcel2003)
            throws Exception {
        imp_status.imp_success = imp_status.imp_totals - imp_status.imp_fails;
        if (!StringUtils.isBlank(imp_status.imp_rows_nomatch)) {
            imp_status.imp_rows_nomatch = imp_status.imp_rows_nomatch.substring(0,
                    imp_status.imp_rows_nomatch.length() - 1);
        }

        if (imp_rows_error.size() > 0) {
            InputStream is = null;
            FileOutputStream out = null;
            InputStream in = null;
            ByteArrayOutputStream bout = null;
            org.apache.poi.ss.usermodel.Workbook wb;
            Sheet sheet;
            Row rows;
            Cell cell;
            String oFFilename = String.format("%s%s%s", tgTempPath, File.separator,
                    String.format("import_err_%s.xls", System.currentTimeMillis()));
            try {
                File sfile = new File("data.xlsx");
                is = new FileInputStream(sfile);
                if (isExcel2003) {
                    wb = new HSSFWorkbook(is);
                    sheet = (HSSFSheet) wb.getSheetAt(0);
                } else {
                    wb = new XSSFWorkbook(is);
                    sheet = (XSSFSheet) wb.getSheetAt(0);
                }
                if (isExcel2003)
                    rows = (HSSFRow) sheet.getRow(startline - 1);
                else
                    rows = (XSSFRow) sheet.getRow(startline - 1);
                int cs = rows.getFirstCellNum();
                int ce = rows.getLastCellNum();

                out = new FileOutputStream(oFFilename);
                HSSFWorkbook w_wb = new HSSFWorkbook();
                HSSFSheet w_sheet = w_wb.createSheet();

                HSSFRow w_row = w_sheet.createRow(0);
                Cell w_cell = w_row.createCell(0);
                w_cell.setCellValue("Error");
                // 根据原excel创建错误excel表头
                int colid = 0;
                for (int s1 = cs; s1 < ce; s1++) {
                    if (!sheet.isColumnHidden(s1)) {
                        colid++;
                        if (isExcel2003)
                            cell = (HSSFCell) rows.getCell(s1);
                        else
                            cell = (XSSFCell) rows.getCell(s1);
                        if (cell != null && cell.getStringCellValue() != null) {
                            w_cell = w_row.createCell(colid);
                            w_cell.setCellValue(cell.getStringCellValue());
                        }
                    }
                }

                // 根据错误信息及原excel创建错误excel数据
                // 提前定义格式
                HSSFCellStyle cellStyle_date = w_wb.createCellStyle();
                HSSFDataFormat hssfdf_date = w_wb.createDataFormat();
                cellStyle_date.setDataFormat(hssfdf_date.getFormat("m/d/yy"));

                HSSFCellStyle cellStyle_datetime = w_wb.createCellStyle();
                HSSFDataFormat hssfdf_datetime = w_wb.createDataFormat();
                cellStyle_datetime.setDataFormat(hssfdf_datetime.getFormat("m/d/yy h:mm"));

                HSSFCellStyle cellStyle_number = w_wb.createCellStyle();
                HSSFDataFormat hssfdf_number = w_wb.createDataFormat();
                cellStyle_number.setDataFormat(hssfdf_number.getFormat("#,##0.00"));

                for (int i = 0; i < imp_rows_error.size(); i++) {
                    String[] row = imp_rows_error.get(i);
                    w_row = w_sheet.createRow(i + 1);
                    w_cell = w_row.createCell(0);

                    int errrownum = Integer.parseInt(row[0]) + 1;
                    w_cell.setCellValue("出错行数：" + errrownum + "，错误原因：" + row[1]);

                    if (isExcel2003)
                        rows = (HSSFRow) sheet.getRow(Integer.valueOf(row[0]));
                    else
                        rows = (XSSFRow) sheet.getRow(Integer.valueOf(row[0]));
                    colid = 0;
                    for (int s1 = cs; s1 < ce; s1++) {
                        if (!sheet.isColumnHidden(s1)) {
                            colid++;
                            if (isExcel2003)
                                cell = (HSSFCell) rows.getCell(s1);
                            else
                                cell = (XSSFCell) rows.getCell(s1);
                            w_cell = w_row.createCell(colid);
                            if (cell != null) {
                                w_cell.setCellValue(cell.getStringCellValue());

                            } else
                                w_cell.setCellValue("");
                            for (String key : icHash.keySet()) {
                                ImportColumnInfo ic = icHash.get(key);
                                if (ic.ici_xls_cols == s1) {
                                    if (ic.ici_xls_type.equalsIgnoreCase("Date")
                                            || ic.ici_xls_type.equalsIgnoreCase("Datetime")
                                            || ic.ici_xls_type.equalsIgnoreCase("Number")) {
                                        if (ic.ici_xls_type.equalsIgnoreCase("Date")) {
                                            w_cell.setCellStyle(cellStyle_date);
                                        } else if (ic.ici_xls_type.equalsIgnoreCase("Datetime")) {
                                            w_cell.setCellStyle(cellStyle_datetime);
                                        } else if (ic.ici_xls_type.equalsIgnoreCase("Number")) {
                                            w_cell.setCellStyle(cellStyle_number);
                                        }
                                    }
                                    break;
                                }
                            }
                        }
                    }
                }
                // 自动调整列宽
                rows = (HSSFRow) w_sheet.getRow(0);
                for (int s1 = rows.getFirstCellNum(); s1 < rows.getLastCellNum(); s1++) {
                    w_sheet.autoSizeColumn((short) s1);
                }
                w_wb.write(out);

                File file = new File(oFFilename);
                in = new FileInputStream(file);
                bout = new ByteArrayOutputStream();
                byte[] b = new byte[1024];
                int n;
                while ((n = in.read(b)) != -1) {
                    bout.write(b, 0, n);
                }

//                String filekey = fileHandleService.save(true, bout.toByteArray(), "xls");
//                imp_status.imp_errfile = filekey;
//
//
//                file.delete();
            } catch (Exception e) {
                throw e;
            } finally {
                if (out != null)
                    out.close();
                if (is != null)
                    is.close();
                if (bout != null)
                    bout.close();
                if (in != null)
                    in.close();
            }
        }
    }

    protected boolean isExist(String clm, HashMap<String, ImportColumnInfo> iciHash) {
        /*[null, taxTaxrate, taxMin, taxCode, taxEdate, taxPosttaxmin, taxAmountmax, taxMax, taxPosttaxmax, comment, taxQkdeuction, taxAmountmin, taxSdate]
         * if (iciHash.get(clm) == null) return true;
         */
        for (String key : iciHash.keySet()) {
            ImportColumnInfo ic = iciHash.get(key);
            if (ic.ici_xls_title.equalsIgnoreCase(clm)) {
                return true;
            }
        }
        return false;
    }


}